package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import Entity.Programme;

public class Dao_Programme {
	private static Connection conn;
	private static Statement sta;
	private static ResultSet rs;

	public static void getConnection() {
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String url = "jdbc:sqlserver://localhost:1130;"
				+ "databaseName=MockProject;";
		String user = "sa";
		String password = "12345678";
		try {
			conn = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void close() {
		if (rs != null) {
			try {
				rs.close();
				if (sta != null)
					sta.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public ArrayList<Programme> getProgram() {
		ArrayList<Programme> ds = new ArrayList<Programme>();
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "select * from Programme ";
			rs = sta.executeQuery(sql);
			String name;
			String des;
			String contact;
			boolean active;
			while (rs.next()) {
				name = rs.getString("name");
				contact = rs.getString("contact");
				des = rs.getString("des");
				active = rs.getBoolean("active");
				Programme pr = new Programme(name, des, contact, active);
				ds.add(pr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
			return ds;
		}
	}

	public ArrayList<Programme> getDsActive(){
		ArrayList<Programme> ds = new ArrayList<Programme>();
		try {
			getConnection();
			sta = conn.createStatement();
			String sql="Select * from Programme WHERE active = 1 ";
			rs= sta.executeQuery(sql);
			String name;
			String des;
			String contact;
			boolean active;
			while(rs.next()){
				name=rs.getString("name");
				des=rs.getString("des");
				contact=rs.getString("contact");
				active=rs.getBoolean("active");
				Programme me = new Programme(name, des, contact, active);
				ds.add(me);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
			return ds;
		}
		
	}
	
	// =======================================================
	// Show default list programme
	public ArrayList<Programme> listDefaultProgramme(boolean status) {
		getConnection();
		ArrayList<Programme> programmeList = new ArrayList<Programme>();
		Programme programme = null;
		try {
			String query = "SELECT * FROM Programme WHERE active = 1";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				programme = new Programme();
				programme.setName(rs.getString("name"));
				programme.setDes(rs.getString("des"));
				programme.setContact(rs.getString("contact"));
				programme.setActive(rs.getBoolean("active"));
				programmeList.add(programme);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
		}
		return programmeList;
	}

	// =========================================================

	// ======================================================
	// Show Programmes By Letter From Fisrt Letter To Second Letter
	public ArrayList<Programme> listProgrammeByLetterFromFisrtLetterToSecondLetter(
			String firstLetter, String secondLetter) {
		getConnection();
		ArrayList<Programme> programmeListByCharacter = new ArrayList<Programme>();
		Programme programme = null;
		try {
			String query = "SELECT * FROM Programme WHERE SUBSTRING(name,1,1) BETWEEN '"
					+ firstLetter + "' AND '" + secondLetter + "'";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				programme = new Programme();
				programme.setName(rs.getString("name"));
				programme.setDes(rs.getString("des"));
				programme.setContact(rs.getString("contact"));
				programme.setActive(rs.getBoolean("active"));
				programmeListByCharacter.add(programme);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
		}
		return programmeListByCharacter;
	}

	// ======================================================
	// Show a detail programme
	public Programme getProgramme(String programmeName) {
		getConnection();
		Programme programme = null;
		try {
			String query = "SELECT * FROM Programme WHERE name='"
					+ programmeName + "'";
			PreparedStatement ps = conn.prepareStatement(query);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				programme = new Programme();
				programme.setName(rs.getString("name"));
				programme.setDes(rs.getString("des"));
				programme.setContact(rs.getString("contact"));
				programme.setActive(rs.getBoolean("active"));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
		}
		return programme;
	}

	public void update(String name) {
		getConnection();
		try {
			sta = conn.createStatement();
			String sql = "UPDATE Programme SET active = 1 WHERE name='" + name
					+ "'";
			sta.executeUpdate(sql);
		} catch (SQLException e) {

		} finally {
			try {
				sta.close();
				conn.close();
			} catch (SQLException ex) {
			}
		}
	}

	public void setDsUpdate(String name, String des, boolean active) {
		// ArrayList<Programme> ds2 = new ArrayList<Programme>();
		try {
			getConnection();
			sta = conn.createStatement();
			String sql = "Update Programme set des='" + des + "', active =";
			if (active) {
				sql += "1 ";
			} else {
				sql += "0 ";
			}
			sql += " WHERE name='" + name + "'";
			sta.executeUpdate(sql);
			sta.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
	}

	public void addProgramme(Programme p) {
		getConnection();
		try {
			String query = "INSERT INTO Programme(name, des, contact, active) VALUES (?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(query);
			ps.setString(1, p.getName());
			ps.setString(2, p.getDes());
			ps.setString(3, p.getContact());
			ps.setBoolean(4, p.isActive());
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.print(e);
		}
	}

public void setDs(String name, String des, String contact, boolean active) {
	// ArrayList<Programme> ds = new ArrayList<Programme>();
	try {
		getConnection();
		sta = conn.createStatement();
		String sql = "Insert Into Programme(name, des, contact, active) values"
				+ "( " + "'" + name + "'," + "'" + des + "'," + "'"
				+ contact + "',";
		//if (active) {
			sql += "1 )";
		//} else {
		//	sql += "0 )";
		//}

		sta.executeUpdate(sql);
		sta.close();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		close();
	}
}
}
